/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2005                    */
/* Created on:     6/10/2011 9:15:46 AM                         */
/*==============================================================*/


if exists (select 1
            from  sysobjects
           where  id = object_id('City')
            and   type = 'U')
   drop table City
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('LookUp')
            and   name  = 'LookupType'
            and   indid > 0
            and   indid < 255)
   drop index LookUp.LookupType
go

if exists (select 1
            from  sysobjects
           where  id = object_id('LookUp')
            and   type = 'U')
   drop table LookUp
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Participants')
            and   type = 'U')
   drop table Participants
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Place')
            and   type = 'U')
   drop table Place
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('PlaceArticle')
            and   name  = 'CreateDate_PK'
            and   indid > 0
            and   indid < 255)
   drop index PlaceArticle.CreateDate_PK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PlaceArticle')
            and   type = 'U')
   drop table PlaceArticle
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PlaceType')
            and   type = 'U')
   drop table PlaceType
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Province')
            and   type = 'U')
   drop table Province
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Road')
            and   type = 'U')
   drop table Road
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('RoadType')
            and   name  = 'RoadTypeDesc_PK'
            and   indid > 0
            and   indid < 255)
   drop index RoadType.RoadTypeDesc_PK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('RoadType')
            and   type = 'U')
   drop table RoadType
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Route')
            and   type = 'U')
   drop table Route
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('RouteArticle')
            and   name  = 'CreateDate_PK'
            and   indid > 0
            and   indid < 255)
   drop index RouteArticle.CreateDate_PK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('RouteArticle')
            and   name  = 'PointArticleID_PK'
            and   indid > 0
            and   indid < 255)
   drop index RouteArticle.PointArticleID_PK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('RouteArticle')
            and   name  = 'ParticipantID_PK'
            and   indid > 0
            and   indid < 255)
   drop index RouteArticle.ParticipantID_PK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('RouteArticle')
            and   name  = 'RouteID_PK'
            and   indid > 0
            and   indid < 255)
   drop index RouteArticle.RouteID_PK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('RouteArticle')
            and   name  = 'RouteDetailID_PK'
            and   indid > 0
            and   indid < 255)
   drop index RouteArticle.RouteDetailID_PK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('RouteArticle')
            and   type = 'U')
   drop table RouteArticle
go

if exists (select 1
            from  sysobjects
           where  id = object_id('RouteDetail')
            and   type = 'U')
   drop table RouteDetail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('RouteEvaluation')
            and   type = 'U')
   drop table RouteEvaluation
go

if exists (select 1
            from  sysobjects
           where  id = object_id('UserGroup')
            and   type = 'U')
   drop table UserGroup
go

/*==============================================================*/
/* Table: City                                                  */
/*==============================================================*/
create table City (
   CityID               int                  identity,
   ProvinceID           int                  null,
   Name                 varchar(30)          not null,
   Level                char(10)             null,
   Position             char(10)             null,
   constraint PK_CITY primary key nonclustered (CityID)
)
go

/*==============================================================*/
/* Table: LookUp                                                */
/*==============================================================*/
create table LookUp (
   LookupID             bigint               not null,
   LookupType           integer              not null,
   LookupDesc           nvarchar(30)         null,
   OptionID             integer              not null,
   OptionDesc           nvarchar(30)         not null,
   constraint PK_LOOKUP primary key nonclustered (LookupID)
)
go

/*==============================================================*/
/* Index: LookupType                                            */
/*==============================================================*/
create clustered index LookupType on LookUp (
LookupType ASC
)
go

/*==============================================================*/
/* Table: Participants                                          */
/*==============================================================*/
create table Participants (
   ParticipantID        bigint               identity,
   GroupID              int                  null,
   NickName             char(100)            not null,
   Email                char(50)             not null,
   Sex                  integer              not null,
   PasswordMD5          char(32)             not null,
   Address              varchar(200)         null,
   Phone                char(12)             null,
   Telphone             char(11)             null,
   RegistDate           datetime             not null,
   State                integer              not null,
   LogonDate            datetime             null,
   LogonMD5             char(32)             null,
   Rewards              bigint               not null default 0,
   constraint PK_PARTICIPANTS primary key nonclustered (ParticipantID)
)
go

/*==============================================================*/
/* Table: Place                                                 */
/*==============================================================*/
create table Place (
   PlaceID              bigint               identity,
   CityID               int                  null,
   Name                 varchar(100)         not null,
   Type                 int                  null,
   Cost                 float                null,
   Position             char(10)             null,
   Popularity           bigint               null,
   constraint PK_PLACE primary key nonclustered (PlaceID)
)
go

/*==============================================================*/
/* Table: PlaceArticle                                          */
/*==============================================================*/
create table PlaceArticle (
   PlaceArticleID       bigint               identity,
   PlaceID              bigint               not null,
   Title                char(100)            null,
   Content              text                 null,
   ParticipantID        bigint               not null,
   CreateDate           datetime             not null,
   PointArticleID       bigint               null,
   Floor                int                  null,
   constraint PK_PLACEARTICLE primary key nonclustered (PlaceArticleID)
)
go

/*==============================================================*/
/* Index: CreateDate_PK                                         */
/*==============================================================*/
create clustered index CreateDate_PK on PlaceArticle (
CreateDate ASC
)
go

/*==============================================================*/
/* Table: PlaceType                                             */
/*==============================================================*/
create table PlaceType (
   PlaceTypeID          int                  identity,
   PlaceTypeDesc        char(20)             not null,
   Comments             varchar(200)         null,
   constraint PK_PLACETYPE primary key nonclustered (PlaceTypeID)
)
go

/*==============================================================*/
/* Table: Province                                              */
/*==============================================================*/
create table Province (
   ProvinceID           int                  identity,
   Name                 varchar(20)          not null,
   Type                 varchar(10)          null,
   Direction            varchar(20)          null,
   constraint PK_PROVINCE primary key nonclustered (ProvinceID)
)
go

/*==============================================================*/
/* Table: Road                                                  */
/*==============================================================*/
create table Road (
   RoadID               bigint               identity,
   "Desc"               varchar(100)         not null,
   RoadTypeID           int                  null,
   constraint PK_ROAD primary key nonclustered (RoadID)
)
go

/*==============================================================*/
/* Table: RoadType                                              */
/*==============================================================*/
create table RoadType (
   RoadTypeID           int                  identity,
   RoadTypeDesc         char(20)             not null,
   Comments             varchar(200)         null,
   constraint PK_ROADTYPE primary key nonclustered (RoadTypeID)
)
go

/*==============================================================*/
/* Index: RoadTypeDesc_PK                                       */
/*==============================================================*/
create clustered index RoadTypeDesc_PK on RoadType (
RoadTypeDesc ASC
)
go

/*==============================================================*/
/* Table: Route                                                 */
/*==============================================================*/
create table Route (
   RouteID              bigint               identity,
   RouteTitle           varchar(100)         not null,
   RouteDesc            text                 null,
   ParticipantID        bigint               not null,
   CreateDate           datetime             not null default getdate(),
   FromPlace            bigint               null,
   ToPlace              bigint               null,
   FromCity             int                  null,
   ToCity               int                  null,
   Popularity           bigint               null,
   constraint PK_ROUTE primary key nonclustered (RouteID)
)
go

/*==============================================================*/
/* Table: RouteArticle                                          */
/*==============================================================*/
create table RouteArticle (
   RouteArticleID       bigint               identity,
   RouteDetailID        bigint               null,
   RouteID              bigint               not null,
   Title                char(100)            null,
   Content              text                 null,
   ParticipantID        bigint               not null,
   CreateDate           datetime             not null,
   PointArticleID       bigint               null,
   Floor                int                  null,
   constraint PK_ROUTEARTICLE primary key nonclustered (RouteArticleID)
)
go

/*==============================================================*/
/* Index: RouteDetailID_PK                                      */
/*==============================================================*/
create index RouteDetailID_PK on RouteArticle (
RouteDetailID ASC
)
go

/*==============================================================*/
/* Index: RouteID_PK                                            */
/*==============================================================*/
create index RouteID_PK on RouteArticle (
RouteID ASC
)
go

/*==============================================================*/
/* Index: ParticipantID_PK                                      */
/*==============================================================*/
create index ParticipantID_PK on RouteArticle (
ParticipantID ASC
)
go

/*==============================================================*/
/* Index: PointArticleID_PK                                     */
/*==============================================================*/
create index PointArticleID_PK on RouteArticle (
PointArticleID ASC
)
go

/*==============================================================*/
/* Index: CreateDate_PK                                         */
/*==============================================================*/
create clustered index CreateDate_PK on RouteArticle (
CreateDate ASC
)
go

/*==============================================================*/
/* Table: RouteDetail                                           */
/*==============================================================*/
create table RouteDetail (
   RouteDetailID        bigint               identity,
   RouteID              bigint               null,
   CityID               int                  null,
   PlaceID              bigint               null,
   RoadID               bigint               null,
   Sequence             int                  not null,
   Introduction         varchar(200)         null,
   GasStationInfo       varchar(100)         null,
   Distance             varchar(100)         null,
   HotelInfo            varchar(100)         null,
   RoadInfo             varchar(100)         null,
   FuelInfo             varchar(100)         null,
   ServiceInfo          varchar(100)         null,
   HelpInfo             varchar(100)         null,
   constraint PK_ROUTEDETAIL primary key nonclustered (RouteDetailID)
)
go

/*==============================================================*/
/* Table: RouteEvaluation                                       */
/*==============================================================*/
create table RouteEvaluation (
   RouteID              bigint               null,
   Evaluator            bigint               not null,
   IPAddress            char(20)             not null,
   Date                 datetime             not null,
   Degree               int                  not null
)
go

/*==============================================================*/
/* Table: UserGroup                                             */
/*==============================================================*/
create table UserGroup (
   GroupID              int                  identity,
   Name                 varchar(50)          not null,
   Authority            int                  not null,
   constraint PK_USERGROUP primary key nonclustered (GroupID)
)
go

